// require
var fs = require("fs");

// functions

function fileToJson(path)
{
  var buf = fs.readFileSync(path);
  var fileString = buf.toString();
  var jf = JSON.parse(fileString);
  return jf;
}

function getFileWriter(fileName)
{
  var fName = fileName;
  return function(dString)
  {
    fs.appendFileSync(fName, dString);
  }
}

function prepareFiles(fileData)
{
  fs.writeFileSync(fileData.fileName, "-- " + fileData.headComment + "\n");
  var d = new Date();
  d = d.toString();
  fileData.writer = getFileWriter(fileData.fileName);
  fileData.writer("-- " + d + "\n");
}

function buildConstrString(constr)
{
  var res = "'" + constr[0] + "'";
  for(var c=1; c<constr.length; c++)
  {
    res += ",\n'" + constr[c] + "'";
  }
  
  res = '( '+res+' )';
  
  return res;
}

function getSqlUpdateFor(type, prop, mapping, model, asList)
{
  var sql;
  var map = mapping[type];
  var table = map["sqlTable"];
  var alias = map["sqlTableAlias"];
  var column = map[prop];
  var constr = model[type][prop];
  
  if(column)
  {
    asList.push({column:column, alias:prop});
  }
  
  if(column && constr)
  {
    constr = buildConstrString(constr);
    sql = {};
    var sqlFrom =
    "from " + table + " " + alias + "\n" +
    "where " + alias + "." + column + " NOT IN " + "\n" +
    constr;
    
    sql.update = 
    "update " + alias + "\n" +
    "set " + alias + "." + column + " = 'N/A'" + "\n" +
    sqlFrom;
    
    sql.select =
    "select distinct " + alias + "." + column + "\n" +
    sqlFrom;
    
  }
  
  return sql;
}

function getSqlForCsv(type, asList, mapping, model)
{
  var sql;
  var map = mapping[type];
  var table = map["sqlTable"];
  var alias = map["sqlTableAlias"];
  
  var sql =
  "select" + "\n";
  
  asList.forEach(function(as, asI)
    {
      var n = (asI < asList.length - 1) ? "',\n" : "'\n";
      sql += alias + "." + as.column + " as " + "'" + as.alias + n;
    });
  
  var comments = getCommentedUnmappedProps(type, asList, mapping, model);
  sql += comments;
  sql += "from " + table + " " + alias;
  return sql;
}

function getCommentedUnmappedProps(type, asList, mapping, model)
{
  var props = model[type];
  var unmapped = [];
  for(p in props)
  {
    unmapped.push(p);
  }
  
  asList.forEach(function(as)
    {
      var t = as.alias;
      unmapped.splice(unmapped.indexOf(t), 1);
    });
  
  var res = "";
  unmapped.forEach(function(um)
    {
      res += "-- " + um + "\n";
    });
  return res;
}

// body

var pathModel = process.argv[2];
var pathMapping = process.argv[3];

if(pathModel && fs.existsSync(pathModel) &&
   pathMapping && fs.existsSync(pathMapping))
{
 
  console.log("-- File found at: ", pathModel);
  
  var jsonModel = fileToJson(pathModel);
  var jsonMapping = fileToJson(pathMapping);
  
  var selects = 
  {
    fileName: "selects.sql",
    headComment: "SELECTS",
    writer: null,
    strings: []
  };
  var updates = 
  {
    fileName: "updates.sql",
    headComment: "UPDATES",
    writer: null,
    strings: []
  };
  var toCsv = 
  {
    fileName: "toCsvGeneration.sql",
    headComment: "SELECTION FOR CSV GENERATION",
    writer: null,
    strings: []
  };
  
  prepareFiles(selects);
  prepareFiles(updates);
  prepareFiles(toCsv);
  
  for(type in jsonMapping)
  {
    var aliasList = [];
    var typeModel = jsonModel[type];
    for(prop in typeModel)
    {
      var sqlRes = getSqlUpdateFor(type, prop, jsonMapping, jsonModel, aliasList);
      if(sqlRes)
      {
        console.log();
      	console.log("-- SELECT DISTINCT for "+type + ", property: "+prop);
      	console.log(sqlRes.select);
      	console.log();
      	console.log("-- UPDATE for "+type + ", property: "+prop);
      	console.log(sqlRes.update);
      	
      	selects.writer("\n");
      	selects.writer("-- SELECT DISTINCT for "+type + ", property: "+prop + "\n");
      	selects.writer(sqlRes.select + "\n");
      	updates.writer("\n");
      	updates.writer("-- UPDATE for "+type + ", property: "+prop + "\n");
      	updates.writer(sqlRes.update + "\n");
      }
    }
    
    var sqlForCsv = getSqlForCsv(type, aliasList, jsonMapping, jsonModel);
    console.log();
    console.log("-- QUERY for csv generation for "+type);
    console.log(sqlForCsv);
    
    toCsv.writer("\n");
    toCsv.writer("-- QUERY for csv generation for "+type+"\n");
    toCsv.writer(sqlForCsv + "\n");
  }
  
}
else
{
  console.log("File NOT found at: ", pathModel);
  console.log("Usage:");
  console.log("node <js> pathToModel.json pathToMapping.json");
}
